package com.netx.ut.lib.hibernate;
import java.util.List;
import java.util.ArrayList;
import java.sql.Date;
import org.hibernate.Session;
import org.hibernate.Query;


@SuppressWarnings("unchecked")
public class Logic {

	private Logic() {}
	
	// TODO use truncate
	public static void truncateOrders(Session s) {
		List<Order> list = s.createQuery("from Order").list();
		for(Order o : list) {
			s.delete(o);
		}
	}

	// TODO use truncate
	public static void truncateCustomers(Session s) {
		List<Customer> list = s.createQuery("from Customer").list();
		for(Customer c : list) {
			s.delete(c);
		}
	}

	// TODO use truncate
	public static void truncateProducts(Session s) {
		List<Product> list = s.createQuery("from Product").list();
		for(Product p : list) {
			s.delete(p);
		}
	}
	
	public static Customer getFirstCustomer(Session s) {
		Query q = s.createQuery("from Customer order by id asc");
		q.setFirstResult(0);
		q.setMaxResults(1);
		return (Customer)q.uniqueResult();
	}
	
	public static List<Order> getOrdersByStatus(Session s, Character status) {
		return s.createQuery("from Order where status = '"+status+"'").list();
	}
	
	public static int dispatchOrdersFor(Session s, Long customerId) {
		Query q = s.createQuery("update Order o set o.status = 'D' where o.customer.id = :cid");
		q.setLong("cid", customerId);
		return q.executeUpdate();
	}

	public static int payOrdersFor(Session s, Long customerId) {
		Query q = s.createQuery("update Order o set o.status = 'P' where o.customer.id = :cid");
		q.setLong("cid", customerId);
		return q.executeUpdate();
	}
	
	public static List<LineItem> getLargerQuantities(Session s) {
		return s.createQuery("from LineItem where quantity > 10 order by quantity asc").list();
	}

	public static List<LineItem> getLineItemsBefore(Session s, Date date) {
		Query q = s.createQuery("from LineItem li inner join li.order o where o.dateMade < ?");
		q.setDate(0, date);
		List<Object[]> list = q.list();
		List<LineItem> results = new ArrayList<LineItem>(list.size());
		for(Object[] o : list) {
			results.add((LineItem)o[0]);
		}
		return results;
	}
	
	public static List<Order> getOrdersFromInactiveCustomers(Session s) {
		//SELECT orders.* FROM orders, customers WHERE orders.customer_id=customers.customer_id AND customers.active='F'		
		Query q = s.createQuery("from Order o inner join o.customer c where c.active = 'F'");
		List<Object[]> list = q.list();
		List<Order> results = new ArrayList<Order>(list.size());
		for(Object[] o : list) {
			results.add((Order)o[0]);
		}
		return results;
	}
	
	public static List<Customer> getActiveCustomers(Session s) {
		return s.createQuery("from Customer c where c.active = 'T' order by c.name asc").list();
	}
	
	public static List<User> getUsersByAge(Session s) {
		return s.createQuery("from User u order by u.age asc, u.city asc, u.name asc").list();
	}

	public static List<User> getUsersByAgeWithLimit(Session s) {
		return getUsersByAgeOnRange(s, 2, 5);
	}

	public static List<User> getUsersByAgeOnRange(Session s, int offset, int numRows) {
		Query q = s.createQuery("from User u order by u.age asc, u.city asc, u.name asc");
		q.setFirstResult(offset);
		q.setMaxResults(numRows);
		return q.list();
	}

	public static List<User> getUsersByName(Session s, String pattern) {
		pattern = pattern.replaceAll("\\*", "%");
		Query q = s.createQuery("from User u where name like ? order by u.name asc");
		q.setString(0, pattern);
		return q.list();
	}

	public static User getUserByUsername(Session s, String username) {
		Query q = s.createQuery("from User u where u.username = ?");
		q.setString(0, username);
		return (User)q.uniqueResult();
	}
	
	public static List<Object[]> getTotalQuantitiesOrdered(Session s) {
		//"SELECT product_id, SUM(quantity) AS total FROM line_items GROUP BY product_id"
		return s.createQuery("select li.product.id, SUM(li.quantity) from LineItem li group by li.product.id order by li.quantity desc, li.product.id asc").list();
	}
}
